package com.DB;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.jasper.tagplugins.jstl.core.Out;

import com.immiinfo.model.*;
import com.mysql.jdbc.PreparedStatement;

//import com.mysql.jdbc.ResultSet;
import java.sql.*;
import java.io.*;
import java.io.IOException;

public class DBHelper {
	public static List<baseInfo> getShowInfo()//显示出所有评估人员信息
	{
		List<baseInfo> resultInfor=new ArrayList<baseInfo>();
		String sql="select * from baseinfo ORDER BY admitdate ASC,totalscore DESC ";
		Connection con;
		try {
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			while(rs.next())
			{
				baseInfo tempInfo=new baseInfo();				
				tempInfo.setName(rs.getString(1));
				tempInfo.setGender(rs.getString(2));
				tempInfo.setCity(rs.getString(3));
				tempInfo.setDataOfBir(rs.getString(4));
				tempInfo.setImiCountry(rs.getString(5));			
				tempInfo.setE_mail(rs.getString(6));
				tempInfo.setTelNumber(rs.getString(7));
				tempInfo.settotalscore(rs.getInt(8));
				tempInfo.setadmitdate(rs.getString(12));
				resultInfor.add(tempInfo);
			}
			jdbcUtil.CloseConn(con, pstmst, rs);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block 
			e.printStackTrace();
		}
		
		return resultInfor;		
	}
	//显示潜在用户信息
	public static List<baseInfo> getImportantInfo(){
		List<baseInfo> resultInfor=new ArrayList<baseInfo>();
		String sql="select * from baseinfo where isimportant=?";
		Connection con;
		try {
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			pstmst.setInt(1, 1);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			while(rs.next())
			{
				baseInfo tempInfo=new baseInfo();				
				tempInfo.setName(rs.getString(1));
				tempInfo.setGender(rs.getString(2));
				tempInfo.setCity(rs.getString(3));
				tempInfo.setDataOfBir(rs.getString(4));
				tempInfo.setImiCountry(rs.getString(5));			
				tempInfo.setE_mail(rs.getString(6));
				tempInfo.setTelNumber(rs.getString(7));
				tempInfo.settotalscore(rs.getInt(8));
				tempInfo.setadmitdate(rs.getString(12));
				resultInfor.add(tempInfo);
			}
			jdbcUtil.CloseConn(con, pstmst, rs);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block 
			e.printStackTrace();
		}
		
		return resultInfor;		
	}
	//变成潜在客户
	public static void addToImportant(String[] deleteEmail)
	{
		String sql="update baseinfo set isimportant=? where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
	    
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setInt(1, 1);
		    	pstmst.setString(2, tempEmail);		
		    	pstmst.executeUpdate();
		    }
		    //String showInfo="已成功将"+deleteEmail.length+"个用户加入潜在用户列表";		    
		    //JOptionPane.showMessageDialog(null, showInfo, "alert", JOptionPane.PLAIN_MESSAGE); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	//从潜在客户列表中移除
	public static void RemoveImportant(String[] deleteEmail)
	{
		String sql="update baseinfo set isimportant=? where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
	    
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setInt(1, 0);
		    	pstmst.setString(2, tempEmail);		
		    	pstmst.executeUpdate();
		    }
		   // String showInfo="已成功将"+deleteEmail.length+"个用户从潜在客户列表移除";
		   // JOptionPane.showMessageDialog(null, showInfo, "alert", JOptionPane.PLAIN_MESSAGE); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
//查看详细，获得基本信息
	public static baseInfo getbaseInfobyemail(String email) throws SQLException
	{
		baseInfo tempInfo=new baseInfo();
		String sql="select * from baseInfo where e_mail=? ";		
		Connection con;	
			con=jdbcUtil.getConnection();//连接数据库
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
            pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			
			while(rs.next()){	
	
				tempInfo.setName(rs.getString(1));
				tempInfo.setGender(rs.getString(2));
				tempInfo.setCity(rs.getString(3));
				tempInfo.setDataOfBir(rs.getString(4));
				tempInfo.setImiCountry(rs.getString(5));			
				tempInfo.setE_mail(rs.getString(6));
				tempInfo.setTelNumber(rs.getString(7));
				tempInfo.settotalscore(rs.getInt(8));
				tempInfo.setIsMarry(rs.getInt(9));
				tempInfo.setPartnerBir(rs.getString(10));
				tempInfo.setqq(rs.getString(13));
				tempInfo.setweixin(rs.getString(14));
				//resultInfor.add(tempInfo);
				
			}
			jdbcUtil.CloseConn(con, pstmst, rs);
			
	
		
		return tempInfo;
				
	}
	//获得本人英语水平
	public static engCap getEngcapbyemail(String email) throws SQLException
	{
		engCap tempInfo=new engCap();
		String sql="select * from engCap where e_mail=?";
		
		Connection con;
		
			con=jdbcUtil.getConnection();//连接数据库
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			    pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			
			
			while(rs.next()){	
	
				//tempInfo.setName(rs.getString(1));
				tempInfo.setEngType(rs.getString(2));
				tempInfo.setListening(rs.getString(3));
				tempInfo.setSpeaking(rs.getString(4));
				tempInfo.setReading(rs.getString(5));			
				tempInfo.setWriting(rs.getString(6));
			
				
				//resultInfor.add(tempInfo);
				
			}
			jdbcUtil.CloseConn(con, pstmst, rs);	
		
		return tempInfo;
				
	}
	//获得配偶英语水平
	public static parengCap getparEngcapbyemail(String email) throws SQLException
	{
		parengCap tempInfo=new parengCap();
		String sql="select * from partnerengcap where e_mail=?";
		
		Connection con;
		
			con=jdbcUtil.getConnection();//连接数据库
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			    pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			
			
			while(rs.next()){	
	
				//tempInfo.setName(rs.getString(1));
				tempInfo.setEngType(rs.getString(2));
				tempInfo.setListening(rs.getString(3));
				tempInfo.setSpeaking(rs.getString(4));
				tempInfo.setReading(rs.getString(5));			
				tempInfo.setWriting(rs.getString(6));
			
				
				//resultInfor.add(tempInfo);
				
			}
			jdbcUtil.CloseConn(con, pstmst, rs);	
		
		return tempInfo;
				
	}
	//获得本人提名职业
		public static tiWorkExper getskillwork(String email) throws SQLException
		{
			tiWorkExper tempInfo=new tiWorkExper();
			String sql="select * from tiWorkExper where e_mail=?";
			
			Connection con;
			
				con=jdbcUtil.getConnection();//连接数据库
				PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
				    pstmst.setString(1, email);
				ResultSet rs=(ResultSet) pstmst.executeQuery();
				
				
				while(rs.next()){	
		
					//tempInfo.setName(rs.getString(1));
					tempInfo.setIn_aus_work(rs.getString(1));
					tempInfo.setOut_aus_work(rs.getString(2));									
					//resultInfor.add(tempInfo);
					
				}
				jdbcUtil.CloseConn(con, pstmst, rs);	
			
			return tempInfo;					
		}
		//配偶提名职业工作经历
		public static parskillwork getparskillwork(String email) throws SQLException
		{
			parskillwork tempInfo=new parskillwork();
			String sql="select * from partnertiworkexper where e_mail=?";
			
			Connection con;
			
				con=jdbcUtil.getConnection();//连接数据库
				PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
				    pstmst.setString(1, email);
				ResultSet rs=(ResultSet) pstmst.executeQuery();
				
				
				while(rs.next()){	
		
					//tempInfo.setName(rs.getString(1));
					tempInfo.setIn_aus_work(rs.getString(1));
					tempInfo.setOut_aus_work(rs.getString(2));									
					//resultInfor.add(tempInfo);
					
				}
				jdbcUtil.CloseConn(con, pstmst, rs);	
			
			return tempInfo;					
		}
		//获得本人学历
		public static List<Mymajor> getmajor(String email) throws SQLException
		{
			List<Mymajor> resultInfor=new ArrayList<Mymajor>();
			
			String sql="select * from myeducation where e_mail=?";			
			Connection con;			
				con=jdbcUtil.getConnection();//连接数据库
				PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
				    pstmst.setString(1, email);
				ResultSet rs=(ResultSet) pstmst.executeQuery();
				while(rs.next()){	
					 Mymajor tempInfo=new  Mymajor();				
					tempInfo.setEduLevel(rs.getString(1));
					tempInfo.setMajor(rs.getString(2));	
					tempInfo.setMajorInCountry(rs.getString(3));
					tempInfo.setMaStartTimeString(rs.getString(4));	
					tempInfo.setMaEndTimeString(rs.getString(5));	
					resultInfor.add(tempInfo);					
				}
				jdbcUtil.CloseConn(con, pstmst, rs);	
			
			return resultInfor;					
		}
		//获得配偶学历
		public static List<par_major> getparmajor(String email) throws SQLException
		{
			List<par_major> resultInfor=new ArrayList<par_major>();
			
			String sql="select * from partnerdeucation where e_mail=?";
			
			Connection con;
			
				con=jdbcUtil.getConnection();//连接数据库
				PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
				    pstmst.setString(1, email);
				ResultSet rs=(ResultSet) pstmst.executeQuery();
				while(rs.next()){	
					par_major tempInfo=new par_major();
					
					tempInfo.setEduLevel(rs.getString(1));
					tempInfo.setMajor(rs.getString(2));	
					tempInfo.setMajorInCountry(rs.getString(3));
					tempInfo.setMaStartTimeString(rs.getString(4));	
					tempInfo.setMaEndTimeString(rs.getString(5));	
					resultInfor.add(tempInfo);
					
				}
				jdbcUtil.CloseConn(con, pstmst, rs);	
			
			return resultInfor;					
		}
	
	public static List<work_experience> getwork_experience(String email) throws SQLException//显示本人工作经历
	{
		List<work_experience> resultInfor=new ArrayList<work_experience>();
		String sql="select job,country,starttime,endtime from work_experience where e_mail=?";
		Connection con;
		
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			
			while(rs.next())
			{
				work_experience tempInfo=new work_experience();
				tempInfo.setJob(rs.getString(1));
				tempInfo.setcountry(rs.getString(2));
				tempInfo.setstarttime(rs.getString(3));
				tempInfo.setendtime(rs.getString(4));
				
				resultInfor.add(tempInfo);
			
			}
			jdbcUtil.CloseConn(con, pstmst, rs);
			
			
		return resultInfor;	
	}
	//显示本人工作经历
	public static List<par_work_experience> getparwork_experience(String email) throws SQLException
	{
		List<par_work_experience> parexpInfor=new ArrayList<par_work_experience>();
		String sql="select par_job,par_country,par_starttime,par_endtime from par_work_experience where e_mail=?";
		Connection con;
		
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();

			while(rs.next())
			{
				par_work_experience tempInfo=new par_work_experience();
				tempInfo.setpar_Job(rs.getString(1));
				tempInfo.setpar_country(rs.getString(2));
				tempInfo.setpar_starttime(rs.getString(3));
				tempInfo.setpar_endtime(rs.getString(4));
				
			  parexpInfor.add(tempInfo);
			}
			jdbcUtil.CloseConn(con, pstmst, rs);
			
			
		return parexpInfor;
		
		
	}
	
	public static List<others> getOthers(String email) throws SQLException//显示本人工作经历
	{
		List<others> resultInfor=new ArrayList<others>();
		String sql="select other from others where e_mail=?";
		Connection con;
		
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			pstmst.setString(1, email);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			
			while(rs.next())
			{
				others tempInfo=new others();
				tempInfo.setOther(rs.getString(1));
				resultInfor.add(tempInfo);
			
			}
			jdbcUtil.CloseConn(con, pstmst, rs);
			
			
		return resultInfor;	
	}
	
	public static int getCount()//获得对象数     
	{
		String sql="select count(*) from baseInfo";
		int count=0;
		Connection con;
		try {
			con=jdbcUtil.getConnection();
			PreparedStatement pstm=(PreparedStatement) con.prepareStatement(sql);
			ResultSet rs=(ResultSet) pstm.executeQuery();
			while(rs.next())
			{
				count=rs.getInt(1);
			}
			jdbcUtil.CloseConn(con, pstm, rs);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
		
	}
	public static int getImportantCount()//获得对象数     
	{
		String sql="select count(*) from baseInfo where isimportant=?";
		int count=0;
		Connection con;
		try {
			con=jdbcUtil.getConnection();
			PreparedStatement pstm=(PreparedStatement) con.prepareStatement(sql);
			pstm.setInt(1,1);
			ResultSet rs=(ResultSet) pstm.executeQuery();
			while(rs.next())
			{
				count=rs.getInt(1);
			}
			jdbcUtil.CloseConn(con, pstm, rs);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
		
	}
	

	public static void deletebyEmails(String[] deleteEmail)
	{
		String sql="delete from baseinfo where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);
		    
		    	pstmst.executeUpdate();
		    }
		  //  String showInfo="已成功将"+deleteEmail.length+"个用户从列表移除";
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsengcap(String[] deleteEmail)
	{
		String sql="delete from engcap where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsworkexp(String[] deleteEmail)
	{
		String sql="delete from work_experience where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsskillwork(String[] deleteEmail)
	{
		String sql="delete from tiworkexper where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsqua(String[] deleteEmail)
	{
		String sql="delete from myeducation where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsOthers(String[] deleteEmail)
	{
		String sql="delete from others where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailspareng(String[] deleteEmail)
	{
		String sql="delete from partnerengcap where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsparwork(String[] deleteEmail)
	{
		String sql="delete from par_work_experience where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsparskillwork(String[] deleteEmail)
	{
		String sql="delete from partnertiworkexper where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	public static void deletebyEmailsparedu(String[] deleteEmail)
	{
		String sql="delete from partnerdeucation where e_mail=?";
		Connection conn;
		  PreparedStatement pstmst;
		try {
		    conn=jdbcUtil.getConnection();
		    pstmst=(PreparedStatement) conn.prepareStatement(sql);
		    for(int i=0;i<deleteEmail.length;i++)
		    {
		    	String tempEmail=deleteEmail[i];
		    	pstmst.setString(1, tempEmail);		   
		    	pstmst.executeUpdate();
		    }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	/**
	 * 将基本信息插入数据库中基本信息表,-1表示插入失败
	 * @param bas
	 * @return
	 */
	public static int InterBaseInfoToDB(baseInfo bas)
	{
		String sql="insert into baseInfo values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, bas.getName());
			pstmst.setString(2, bas.getGender());
			pstmst.setString(3, bas.getCity());
			pstmst.setString(4,bas.getDateOfBir());
			pstmst.setString(5, bas.getImiCountry());
			pstmst.setString(6, bas.getE_mail());
			pstmst.setString(7, bas.getTelNumber());
			pstmst.setInt(8,bas.getTotalscore());
			pstmst.setInt(9, bas.getIsMarry());
			pstmst.setString(10, bas.getPartnerBir());
			pstmst.setInt(11, 0);
			pstmst.setString(12,bas.getadmitdate());
			pstmst.setString(13,bas.getqq());
			pstmst.setString(14,bas.getweixin());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 将英语能力信息添加到engCap表中
	 * @param engcap
	 * @return
	 */
	public static int InsertEngCapToDB(engCap engcap)
	{
		String sql="insert into engCap values(?,?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, engcap.getE_mail());
			pstmst.setString(2, engcap.getEngType());
			pstmst.setString(3, engcap.getListening());
			pstmst.setString(4, engcap.getSpeaking());
			pstmst.setString(5, engcap.getReading());
			pstmst.setString(6, engcap.getWriting());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将工作经历信息添加到workExper表中
	 * @param workExper
	 * @return
	 */
	public static int InsertworkExperToDB(workExper wor)
	{
		String sql="insert into work_experience values(?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, wor.getEmail());
			pstmst.setString(2, wor.getJob());
			pstmst.setString(4, wor.getStartTime());
			pstmst.setString(5, wor.getEndTime());
			pstmst.setString(3, wor.getWork_country());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将在与提名职业相关的澳大利亚境外工作经历插入到表TiWorkExper
	 * @param wor
	 * @return
	 */
	public static int InsertTiWorkExperToDB(tiWorkExper tiwor)
	{
		String sql="insert into TiWorkExper values(?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, tiwor.getIn_aus_work());
			pstmst.setString(2, tiwor.getOut_aus_work());
			pstmst.setString(3, tiwor.getEmail());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将个人的教育信息存入数据库中
	 * @param major
	 * @return
	 */
	public static int InsertEduLevelToDB(Mymajor major)
	{
		String sql="insert into Myeducation values(?,?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, major.getEduLevel());
			pstmst.setString(2, major.getMajor());
			pstmst.setString(3, major.getMajorInCountry());
			pstmst.setString(4, major.getMaStartTimeString());
			pstmst.setString(5, major.getMaEndTimeString());
			pstmst.setString(6, major.getEmail());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将个人的其他信息存入数据库中
	 * @param otherList
	 * @return
	 */
	public static int InsertOthersToDB(List<others> otherList)
	{
		String sql="insert into others values(?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			for(int i=0;i<otherList.size();i++)
			{
				pstmst.setString(1, otherList.get(i).getEmail());
				pstmst.setString(2, otherList.get(i).getOther());
				flag=pstmst.executeUpdate();
				if(flag<0)
				{
					break;
				}
				
			}
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 将配偶英语考试信息存入对应的数据库中
	 * @param engcap
	 * @return
	 */
	public static int InsertPartnerEngCapToDB(engCap engcap)
	{
		String sql="insert into partnerEngCap values(?,?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, engcap.getE_mail());
			pstmst.setString(2, engcap.getEngType());
			pstmst.setString(3, engcap.getListening());
			pstmst.setString(4, engcap.getSpeaking());
			pstmst.setString(5, engcap.getReading());
			pstmst.setString(6, engcap.getWriting());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将配偶的工作经验插入到其对应的表中
	 * @param wor
	 * @return
	 */
	public static int InsertPartnerWorkExperToDB(workExper wor)
	{
		String sql="insert into par_work_experience values(?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, wor.getEmail());
			pstmst.setString(2, wor.getJob());
			pstmst.setString(4, wor.getStartTime());
			pstmst.setString(5, wor.getEndTime());
			pstmst.setString(3, wor.getWork_country());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将配偶在与提名职业相关的澳大利亚境外工作经历插入到表TiWorkExper
	 * @param wor
	 * @return
	 */
	public static int InsertParTiWorkExperToDB(tiWorkExper tiwor)
	{
		String sql="insert into partnerTiWorkExper values(?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, tiwor.getIn_aus_work());
			pstmst.setString(2, tiwor.getOut_aus_work());
			pstmst.setString(3, tiwor.getEmail());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 将配偶的教育信息存入数据库中
	 * @param major
	 * @return
	 */
	public static int InsertParEduLevelToDB(Mymajor major)
	{
		String sql="insert into Partnerdeucation values(?,?,?,?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, major.getEduLevel());
			pstmst.setString(2, major.getMajor());
			pstmst.setString(3, major.getMajorInCountry());
			pstmst.setString(4, major.getMaStartTimeString());
			pstmst.setString(5, major.getMaEndTimeString());
			pstmst.setString(6, major.getEmail());
			flag=pstmst.executeUpdate();
			jdbcUtil.CloseConn2(conn, pstmst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 从eva_items中删除question=other，answer=answer的数据
	 * @param answer
	 * @return
	 */
	public static int deleteItemsValueFromDB(String answer)
	{
		String sql="delete from eva_items where answer=?";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, answer);
			flag=pstmst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 * 向eva_items中增加新的answer，question=other
	 * @param answer
	 * @return
	 */
	public static int insertNewQuestIntoDB(String answer,int score)
	{
		String sql="insert into eva_items (question,answer,score) values(?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, "其它");
			pstmst.setString(2, answer);
			pstmst.setInt(3, score);
			flag=pstmst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	//增加新的移民信息
	public static int insertCountrytoDB(String country)
	{
		String sql="insert into immicountry (country) values(?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, country);
			flag=pstmst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	//删除移民信息
	public static int deleteCountrytoDB(String country)
	{
		String sql="delete from immicountry where country=?";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, country);
			flag=pstmst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	/**
	 *  向eva_items中增加新评估项目
	 * @param question
	 * @param answer
	 * @param score
	 * @return
	 */
	public static int insertNewItemInDB(String question,String answer,int score)
	{
		String sql="insert into eva_items (question,answer,score) values(?,?,?)";
		int flag=-1;
		Connection conn;
		try {
			conn=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) conn.prepareStatement(sql);
			pstmst.setString(1, question);
			pstmst.setString(2, answer);
			pstmst.setInt(3, score);
			flag=pstmst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public static List<immiCountry> getCountry()
	{
		List<immiCountry> resultInfor=new ArrayList<immiCountry>();
		String sql="select * from immicountry";
		Connection con;
		try {
			con=jdbcUtil.getConnection();
			PreparedStatement pstmst=(PreparedStatement) con.prepareStatement(sql);
			ResultSet rs=(ResultSet) pstmst.executeQuery();
			while(rs.next())
			{
				immiCountry tempInfo=new immiCountry();								
				tempInfo.setCountry(rs.getString(1));				
				resultInfor.add(tempInfo);
			}
			jdbcUtil.CloseConn(con, pstmst, rs);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block 
			e.printStackTrace();
		}		
		return resultInfor;		
	}
	
	public static void main(String []args) throws SQLException
	{
		
		
	}

}
